package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.proactive.webapp.dao.LightDAO;
import com.proactive.webapp.entity.Card;
import com.proactive.webapp.entity.Light;
import com.proactive.webapp.entity.Member;

public class LightDAOImpl implements LightDAO {
	private DataSource dataSource;
	private Connection conn = null;
	private ResultSet rs = null;
	private PreparedStatement smt = null;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public int insert(Light light) {
		// TODO Auto-generated method stub
		String sql = "INSERT light(lightTemplateID,lightSender,lightReceiver,lightContent,lightStatus,memberAccount,lightQuestion,lightAnswer,createDate) "
				+ "VALUES(?,?,?,?,?,?,?,?,NOW())";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, light.getTid());
			smt.setString(2, light.getSender());
			smt.setString(3, light.getReceiver());
			smt.setString(4, light.getContent());
			smt.setInt(5, light.getStatus());
			smt.setString(6, light.getAccount());
			smt.setString(7, light.getQuestion());
			smt.setString(8, light.getAnswer());
			
			smt.executeUpdate();			
			smt.close();
			
			String sql2 = "SELECT LAST_INSERT_ID() AS lightID";//回傳此次新增的pk
			int id = 0;
			smt=conn.prepareStatement(sql2);
			rs = smt.executeQuery();
			if(rs.next()){
				id = rs.getInt("lightID");
			}
			
			smt.close();
			conn.close();
			return id;
		
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
	}
	
	@Override
	public void delete(Light light) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM light WHERE lightID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, light.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public List<Light> getList() {
		// TODO Auto-generated method stub
		List<Light> lightList = new ArrayList<Light>();
		String sql = "SELECT a.*,b.* FROM light a JOIN light_template b ON a.lightTemplateID=b.lightTemplateID";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			rs = smt.executeQuery();
			while(rs.next()){
				Light light = new Light();
				light.setId(rs.getInt("lightID"));
				light.setTid(rs.getInt("lightTemplateID"));
				light.setSender(rs.getString("lightSender"));
				light.setReceiver(rs.getString("lightReceiver"));
				light.setContent(rs.getString("lightContent"));
				light.setStatus(rs.getInt("lightStatus"));
				light.setAccount(rs.getString("memberAccount"));
				light.setQuestion(rs.getString("lightQuestion"));
				light.setAnswer(rs.getString("lightAnswer"));
				light.setImagePath(rs.getString("lightTemplateImage"));
				lightList.add(light);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return lightList;
	}
	
	public int getTotal(){
		Connection conn = null;
		PreparedStatement smt = null;
		List<Light> lightList = new ArrayList<Light>();
		
		String sql = "SELECT COUNT(lightID) AS total FROM light";
		int total = 0;
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			ResultSet rs1 = smt.executeQuery();
			if(rs1.next()){
				total = rs1.getInt("total");				
			}
			rs1.close();
			smt.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}		
		return total;
	}
	
	public List<Light> getNextTenList(int now,int id) {
		Connection conn = null;
		PreparedStatement smt = null;
		List<Light> lightList = new ArrayList<Light>();
		
		String sql = "SELECT a.*,b.* FROM light a JOIN light_template b ON a.lightTemplateID=b.lightTemplateID ORDER BY lightID Limit ?,?";
		String sql2 = "SELECT a.*,b.* FROM light a JOIN light_template b ON a.lightTemplateID=b.lightTemplateID WHERE lightID = ?";
		
		int mount = 10;	
		boolean flag = false;
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, now);
			smt.setInt(2, mount);
			ResultSet rs1 = smt.executeQuery();

			while(rs1.next()){
				Light light = new Light();
				light.setId(rs1.getInt("lightID"));
				light.setTid(rs1.getInt("lightTemplateID"));
				light.setSender(rs1.getString("lightSender"));
				light.setReceiver(rs1.getString("lightReceiver"));
				light.setContent(rs1.getString("lightContent"));
				light.setStatus(rs1.getInt("lightStatus"));
				light.setQuestion(rs1.getString("lightQuestion"));
				light.setAnswer(rs1.getString("lightAnswer"));
				light.setImagePath(rs1.getString("lightTemplateImage"));
				light.setFloatPath(rs1.getString("floatPath"));
				lightList.add(light);
				if(light.getId()==id){//有重複、不代id(=0)
					flag = true;
				}									
			}
			
			if(lightList.size()!=10){
				now = 0;
				mount = 10-lightList.size();
				try {
					smt.setInt(1, now);
					smt.setInt(2, mount);
					ResultSet rs2 = smt.executeQuery();
					while(rs2.next()){
						Light light = new Light();
						light.setId(rs2.getInt("lightID"));
						light.setTid(rs2.getInt("lightTemplateID"));
						light.setSender(rs2.getString("lightSender"));
						light.setReceiver(rs2.getString("lightReceiver"));
						light.setContent(rs2.getString("lightContent"));
						light.setStatus(rs2.getInt("lightStatus"));
						light.setQuestion(rs2.getString("lightQuestion"));
						light.setAnswer(rs2.getString("lightAnswer"));
						light.setImagePath(rs2.getString("lightTemplateImage"));
						light.setFloatPath(rs2.getString("floatPath"));
						lightList.add(light);
						if(light.getId()==id){//有重複、不代id(=0)
							flag = true;
						}
					}		
				} catch (SQLException e) {
					throw new RuntimeException(e);		 
				}
			}
			
			if(flag==false){
				try {
					smt = conn.prepareStatement(sql2);
					smt.setInt(1, id);
					ResultSet rs3 = smt.executeQuery();
					if(rs3.next()){
						Light light = new Light();
						light.setId(rs3.getInt("lightID"));
						light.setTid(rs3.getInt("lightTemplateID"));
						light.setSender(rs3.getString("lightSender"));
						light.setReceiver(rs3.getString("lightReceiver"));
						light.setContent(rs3.getString("lightContent"));
						light.setStatus(rs3.getInt("lightStatus"));
						light.setQuestion(rs3.getString("lightQuestion"));
						light.setAnswer(rs3.getString("lightAnswer"));
						light.setImagePath(rs3.getString("lightTemplateImage"));
						light.setFloatPath(rs3.getString("floatPath"));
						lightList.remove(lightList.size()-1);					
						lightList.add(lightList.size(), light);
					}
					rs3.close();
					smt.close();
			 
				} catch (SQLException e) {
					throw new RuntimeException(e);		 
				}	
			}
			
			rs1.close();
			smt.close();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}		
		return lightList;
	}
	
	@Override
	public List<Light> getListByMember(String account) {
		// TODO Auto-generated method stub
		List<Light> lightList = new ArrayList<Light>();
		String sql = "SELECT a.*,b.* FROM light a JOIN light_template b ON a.lightTemplateID=b.lightTemplateID WHERE memberAccount = ? ORDER BY a.lightID DESC";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			while(rs.next()){
				Light light = new Light();
				light.setId(rs.getInt("lightID"));
				light.setTid(rs.getInt("lightTemplateID"));
				light.setSender(rs.getString("lightSender"));
				light.setReceiver(rs.getString("lightReceiver"));
				light.setContent(rs.getString("lightContent"));
				light.setStatus(rs.getInt("lightStatus"));
				light.setQuestion(rs.getString("lightQuestion"));
				light.setAnswer(rs.getString("lightAnswer"));
				light.setImagePath(rs.getString("lightTemplateImage"));
				light.setFloatPath(rs.getString("floatPath"));
				lightList.add(light);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return lightList;
	}

	@Override
	public Light get(Light light) {
		// TODO Auto-generated method stub
		String sql = "SELECT * FROM light a JOIN member b ON a.memberAccount = b.memberAccount "
				+ "WHERE lightID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, light.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setMemberNO(rs.getString("memberNO"));
				member.setName(rs.getString("memberName"));
				light.setMember(member);
				light.setTid(rs.getInt("lightTemplateID"));
				light.setSender(rs.getString("lightSender"));
				light.setReceiver(rs.getString("lightReceiver"));
				light.setContent(rs.getString("lightContent"));
				light.setStatus(rs.getInt("lightStatus"));
				light.setAccount(rs.getString("memberAccount"));
				light.setQuestion(rs.getString("lightQuestion"));
				light.setAnswer(rs.getString("lightAnswer"));
//				light.setImagePath(rs.getString("lightTemplateImage"));
			
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return light;
	}
	
	
}

